package com.cetcs.kmga.log.util;

import com.alibaba.druid.pool.DruidPooledConnection;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.List;
import java.util.Map;

/**
 * Created by XingWen on 2017/5/13.
 */
public class MySQLClient {

    private final static Logger LOGGER = LoggerFactory.getLogger(MySQLClient.class);

    private static DruidPooledConnection connection = getConnection();

    public static DruidPooledConnection getConnection() {
        try {
            if (connection == null) {
                //connection = MySQLDruidPool.getInstance().getMySQLConnection();
                connection = MySQLDruidPool.getInstance().getConnection();
                //connection = JdbcUtils.getConnection();
                connection.setAutoCommit(false);
            }
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
        return connection;
    }

    /**
     * 添加条记录
     *
     * @param tbName 表名
     * @param params 参数
     * @return
     */
    public static int addRecord(String tbName, Map<String, Object> params) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        int ret = 0;
        try {
            connection = MySQLClient.getConnection();
            //开始组装SQL
            String sql = " INSERT INTO " + tbName + " ( ";
            if (params == null || params.isEmpty()) {
                LOGGER.error("参数不能为空");
                close();
                ret = -1;
                return ret;
            }
            int i = 0;
            String placeholderStr = "";
            for (Map.Entry<String, Object> entry : params.entrySet()) {
                if (i != 0) {
                    sql += " , ";
                    placeholderStr += " ,";
                }
                sql += entry.getKey();
                placeholderStr += "?";
                i++;
            }
            sql += " ) VALUES (" + placeholderStr + ")";
            LOGGER.info("sql:" + sql);
            statement = connection.prepareStatement(sql);
            int j = 1;
            for (Map.Entry<String, Object> entry : params.entrySet()) {
                statement.setObject(j, entry.getValue());
                j++;
            }
            statement.executeUpdate();
            connection.commit();
            rs = statement.getGeneratedKeys();
            if (rs.next()) {
                ret = rs.getInt(1);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
            ret = -1;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            close();
        }
        return ret;
    }

    /**
     * 添加条记录
     *
     * @param tbName 表名
     * @param titles 字段
     * @param data   数据
     * @return
     */
    public static int addBathRecord(String tbName, List<String> titles, List<List<String>> data) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet rs = null;
        int ret = 0;
        try {
            connection = MySQLClient.getConnection();
            //开始组装SQL
            String sql = " INSERT INTO " + tbName + " ( ";
            if (titles == null || titles.size() == 0) {
                LOGGER.error("字段不能为空");
                close();
                ret = -1;
                return ret;
            }

            String placeholderStr = "";
            for (int i = 0; i < titles.size(); i++) {
                if (i != 0) {
                    sql += " , ";
                    placeholderStr += " ,";
                }
                sql += titles.get(i);
                placeholderStr += "?";
            }
            sql += " ) VALUES (" + placeholderStr + ")";
            LOGGER.info("sql:" + sql);
            statement = connection.prepareStatement(sql);
            for (int i = 1; i < data.size(); i++) {//100万条数据
                List<String> pojo = data.get(i);
                for (int j = 0; j < pojo.size(); j++) {
                    statement.setObject(j+1, pojo.get(j));
                }
                statement.addBatch();
                if (i % 1000 == 0) {
                    statement.executeBatch();
                }
            }
            statement.executeBatch();
            connection.commit();
        } catch (SQLException ex) {
            ex.printStackTrace();
            ret = -1;
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            close();
        }
        return ret;
    }

    /**
     * 关闭Hbase
     */
    public static void close() {
        if (connection != null) {
            try {
                connection.close();
                connection = null;
            } catch (Exception e) {
            }
        }
    }


    public static List<Map<String, Object>> query(String sql) {
        List<Map<String, Object>> list = Lists.newArrayList();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet rs = preparedStatement.executeQuery();
            ResultSetMetaData rsme = rs.getMetaData();
            int columnCount = rsme.getColumnCount();
            while (rs.next()) {
                Map<String, Object> rowData = Maps.newHashMap();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(rsme.getColumnName(i).toUpperCase(), rs.getObject(i));
                }
                list.add(rowData);
                break;
            }
            return list;
        } catch (Exception ex) {
            ex.printStackTrace();
            return list;
        }
    }

    public static Map<String, Object> queryMTitles(String sql) {
        Connection conn = getConnection();
        Map<String, Object> rowData = Maps.newHashMap();
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData(); //元数据
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(rsmd.getColumnName(i).toUpperCase(), rs.getObject(i));
                }
                break;
            }
            conn.commit();
            rs.close();
            pstmt.close();
            return rowData;
        } catch (Exception ex) {
            return rowData;
        } finally {
            close();
        }
    }

    public static List<Map<String, Object>> queryMDatas(String sql) {
        List<Map<String, Object>> list = Lists.newArrayList();
        Connection conn = getConnection();
        try {
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ResultSet rs = pstmt.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData(); //元数据
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                Map<String, Object> rowData = Maps.newHashMap();
                for (int i = 1; i <= columnCount; i++) {
                    rowData.put(rsmd.getColumnName(i).toUpperCase(), rs.getObject(i));
                }
                list.add(rowData);
            }
            conn.commit();
            rs.close();
            pstmt.close();
            return list;
        } catch (Exception ex) {
            return list;
        } finally {
            close();
        }
    }


}
